import os

import pymysql

from Base import GetYamlFile
from Base.GetYamlFile import getYamlPath
from Base.ReadExcel import Read_Excel
from Base.WriteExcel import Write_excel

yml_list = GetYamlFile.getYamlFile()
xiaoshouyingshouPath = yml_list["xiaoshouyingshouPath"]
PROJECT_ROOT = os.path.dirname(os.path.dirname(__file__))
REPORT_XLSX_DIR = os.path.join(PROJECT_ROOT, xiaoshouyingshouPath)
readBook = Read_Excel(REPORT_XLSX_DIR)

sql_config = getYamlPath("SqlConfigBasic")
basic_conn = pymysql.connect(**sql_config)
cur = basic_conn.cursor()
num = readBook.get_RowNums("sheet")

list1 = []
list2 = []
for i in range(4, num):
    name = readBook.read_Excel("sheet", i, 3)
    money = readBook.read_Excel("sheet", i, 23)
    if name != '':
        data = {
            'name': name,
            'money': -money
        }
        list1.append(name)
        list2.append(data)
select_sql = '''
        select `name` from bs_customer where `name` in {} ;
'''.format(tuple(list1))

test = cur.execute(select_sql)
test = cur.fetchall()
list3 = []
for item in test:
    list3.append(item[0])
list4 = []
for item in list2:
    if item.get("name") in list3:
        list4.append(item)
print(list4)

xiaoshouyingshoudaoruPath1 = yml_list["xiaoshouyingshoudaoruPath1"]
PROJECT_ROOT = os.path.dirname(os.path.dirname(__file__))
REPORT_XLSX_DIR = os.path.join(PROJECT_ROOT, xiaoshouyingshoudaoruPath1)
readBook = Read_Excel(REPORT_XLSX_DIR)

num = readBook.get_RowNums("期初金额导入模板")
list5 =[]
for i in range(1,num):
    list5.append(readBook.read_Excel("期初金额导入模板", i, 1))

print(list5)


xiaoshouyingshoudaoruPath = yml_list["xiaoshouyingshoudaoruPath"]
PROJECT_ROOT = os.path.dirname(os.path.dirname(__file__))
REPORT_XLSX_DIR = os.path.join(PROJECT_ROOT, xiaoshouyingshoudaoruPath)
readBook = Read_Excel(REPORT_XLSX_DIR)

num = readBook.get_RowNums("期初金额导入模板")
wt = Write_excel(REPORT_XLSX_DIR, "期初金额导入模板")
for i in range(1, num):
    name = readBook.read_Excel("期初金额导入模板", i, 1)

    for ls in list4:
        if name == ls.get("name"):
            print(ls.get("name"))
            print(ls.get("money"))
            print(i)
            if name not in list5:
                wt.write(i + 1, 6, ls.get("money"))
